#!/usr/bin/env python
# -*- coding: utf-8 -*-

# @Author  : linlvfei2008@126.com
# @Time    : 2019/7/6 10:01
# @Site    : https://gitee.com/chunhuayuyun
from collections import namedtuple

import pymysql


class MyDB:
    """
    为了增强代码可读性，推荐使用execute_select来执行查询语句
    """

    def __init__(self):
        self.db = None
        self.cursor = None

    @classmethod
    def select_db(cls, select_db="old", db_name="quanfan"):
        """
        :param select_db:选择老系统还是信系统
        :param db_name:
        :return:
        """
        if select_db == "old":
            # 老系统正式
            # config = {
            #     'host': 'rm-wz9702t7022avreborw.mysql.rds.aliyuncs.com',
            #     'user': 'readonly_xb',
            #     'passwd': 'zjxb@@$$588',
            #     'port': 3306,
            #     'db': db_name,
            #     'charset': 'utf8'
            # }
            # 老系统备份
            config = {
                'host': 'rm-bp1g3307unb5t8tk5po.mysql.rds.aliyuncs.com',
                'user': 'old_db',
                'passwd': 'old_db123456',
                'port': 3306,
                'db': 'quanfan',
                'charset': 'utf8'
            }
        elif select_db == 'new':
            # 正式
            config = {
                'host': 'pc-bp12p6374x2wr261r.rwlb.rds.aliyuncs.com',
                'user': 'readonly_prod',
                'passwd': 'j2i3WJQwMzjA1lSFcMhjyZr9jxfEfr8Z',
                'port': 3306,
                'db': db_name,
                'charset': 'utf8'
            }
            # 非正式
            # config = {
            #     'host': '172.30.1.228',
            #     'user': 'root',
            #     'passwd': 'aa123456',
            #     'port': 3306,
            #     'db': db_name,
            #     'charset': 'utf8'
            # }
        else:
            raise ValueError("old = 老系统， new = 新系统")

        return config

    def connect_database(self, select_db, db_name):
        """
        connect to database
        :return:
        """
        config = self.select_db(select_db, db_name)
        try:
            # connect to DB
            self.db = pymysql.connect(**config)
            # create cursor
            self.cursor = self.db.cursor()
        except ConnectionError as ex:
            raise ConnectionError("数据库连接异常！" + str(ex))

    def execute_select(self, select_db="old", db_name="quanfan", sql_text="", is_print_result=False):
        """
        :param select_db:选择老系统还是新系统
        :param db_name: 数据库名称
        :param sql_text: 查询语句，只能执行查询
        :param is_print_result: 是否输出查询信息到控制台
        :return: name_tuple列表
        """
        # 转小写
        sql_text_low = sql_text.lower()
        # 分隔字符串，来判断是否是查询语句
        sql_text_strip = sql_text_low.split(' ')

        if "select" == sql_text_strip[0]:
            print("执行SQL：", sql_text)
            # 连接数据库
            db_name = str(db_name).lower()
            db_name = db_name.replace('test_', "")
            # if self.db_name == "data_base_test":
            #     db_name = "test_" + db_name
            self.connect_database(select_db, db_name)
            # 创建指针
            self.cursor.execute(sql_text)
            # 字段详情
            des = self.cursor.description
            # 获取所有字段名
            field_list = []
            for item in des:
                if item[0] in ['raise', 'from']:
                    field_list.append(item[0] + '_')
                else:
                    field_list.append(item[0])
            if str(field_list).find("(") > 0:
                raise ValueError("查询语句错误，请命名各个字段如：sum(account) as xx")

            result = self.cursor.fetchall()

            # 关闭游标与连接
            self.cursor.close()
            self.db.close()

            result = self.list_to_namedtuple(field_list, result)
            if is_print_result:
                print("查询结果：", str(result))
            return result
        else:
            raise ValueError("只能执行查询语句，无法执行删除，更新等操作")

    def execute_select1(self, select_db="old", db_name="quanfan", sql_text="", is_print_result=True):
        """
        查询可提现金额=资金总额*服务费比例
        """

        # 转小写
        # sql_text_low = sql_text.lower()
        # 分隔字符串，来判断是否是查询语句
        # sql_text_strip = sql_text_low.split(' ')

        # if "select" == sql_text_strip[0]:
        # print("执行SQL：", sql_text)
        # 连接数据库
        # if self.db_name == "data_base_test":
        #     db_name = "test_" + db_name

        self.connect_database(select_db, db_name)
        # 创建指针
        self.cursor.execute(sql_text)
        # 字段详情
        des = self.cursor.description

        # 获取所有字段名
        field_list = []
        for item in des:
            if item[0] in ['raise', 'from']:
                field_list.append(item[0] + '_')
            else:
                field_list.append(item[0])
        result = self.cursor.fetchall()

        # 关闭游标与连接
        self.cursor.close()
        self.db.close()

        result = self.list_to_namedtuple(field_list, result)
        if is_print_result:
            print("查询结果：", str(result))
            return result

    @staticmethod
    def list_to_namedtuple(field_name_list, list_data):
        """
        :param field_name_list: 字段列表
        :param list_data: 数据列表
        :return:
        """
        if len(list_data) == 0:
            return []
        namedtuple_list = []
        namedtuple_table = namedtuple("select_info", field_name_list)

        for t in list_data:
            namedtuple_one = namedtuple_table(*t)
            namedtuple_list.append(namedtuple_one)
        return namedtuple_list


if __name__ == '__main__':
    # 实例化对象
    test = MyDB()
    sql = "select * FROM common_coin_type"
    # 执行查询操作，返回 列表
    list_a = test.execute_select("coin", sql)
    # 取列表的第一项
    b = list_a[0]

